package ibms_rcp.dao;

import ibms_rcp.constants.Constants;
import ibms_rcp.db.DataBaseManager;
import ibms_rcp.model.Company;
import ibms_rcp.model.Lease;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 客户资料DAO
 * @author diamond-wang
 *
 */
public class CompanyManager {
	/**
	 * 保存company
	 * @param company
	 */
	public static void save(Company company){
		//连接数据库
		Connection conn = DataBaseManager.getConnection();
		try {
			//禁止自动提交
			conn.setAutoCommit(false);
			//插入company的moi
			String moiCompanySql = "insert into m2mnap_moi (id, moc_id, name, hash_code, native_ems_name, native_ems_id, status, status_time, created_time) " +
					"values(nextval('m2mnap_moi_seq'), ?, ?, ?, ?, ?, 1, now(), now()) returning id;";
			PreparedStatement pstmt = conn.prepareStatement(moiCompanySql);
			pstmt.setLong(1, Constants.MOC_COMPANY_ID);
			pstmt.setString(2, "/公司="+company.getName());
			pstmt.setString(3, String.valueOf(company.hashCode()));
			pstmt.setString(4, company.getName());
			pstmt.setString(5, company.getCode());
			//返回插入的company moi的id
			long moiid = 0;
			ResultSet rs = pstmt.executeQuery();
			if(rs.next())
				moiid = rs.getLong(1);
			company.setId(moiid);
			rs.close();
			pstmt.close();
			
			//插入company的attribute
			String companyAttrSql = "insert into m2mnap_moi_attribute (id, moc_id, moi_id, old_value, new_value, last_changed_time, moc_attribute_code) " +
					"values (nextval('m2mnap_moi_attribute_seq'), ?, ?, '', ?, now(), ?);";
			pstmt = conn.prepareStatement(companyAttrSql);
			pstmt.setLong(1, Constants.MOC_COMPANY_ID);
			pstmt.setLong(2, moiid);
			pstmt.setString(3, company.getName());//公司名称
			pstmt.setString(4, "name");
			pstmt.executeUpdate();
			pstmt.setString(3, company.getCode());//公司编号
			pstmt.setString(4, "Id");
			pstmt.executeUpdate();
			pstmt.setString(3, company.getPer());//联系人
			pstmt.setString(4, "per");
			pstmt.executeUpdate();
			pstmt.setString(3, company.getTel());//联系电话
			pstmt.setString(4, "tel");
			pstmt.executeUpdate();
			pstmt.setString(3, company.getPhone());//座机
			pstmt.setString(4, "phone");
			pstmt.executeUpdate();
			pstmt.setString(3, "硅谷亮城");//地址
			pstmt.setString(4, "addr");
			pstmt.executeUpdate();
			pstmt.setDouble(3, company.getBalance());//预存款
			pstmt.setString(4, "bookSaving");
			pstmt.executeUpdate();
			pstmt.setInt(3, company.getRemindDay());//提醒阈值
			pstmt.setString(4, "remindDay");
			pstmt.executeUpdate();
			pstmt.close();
			//提交
			conn.commit();
			System.out.println("插入记录公司(moi_id, 公司名称, 公司编号, 联系人, 联系电话, 座机, 预存款, 提醒阈值) values ("
					+ moiid +", " + company.getName() +", " + company.getId() +", " + company.getPer() +", " + company.getTel() +", "
					+ company.getPhone() +", " + company.getBalance() +", " + company.getRemindDay() + ");");
		} catch (SQLException e) {
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		System.out.println("CompanyManager save() called.");
	}
	
	/**
	 * 更新company
	 * @param company
	 */
	public static void update(Company company){
		//连接数据库
		Connection conn = DataBaseManager.getConnection();
		try {
			//禁止自动提交
			conn.setAutoCommit(false);
			//插入company的moi
			long companyId = company.getId();
			String moiCompanySql = "update m2mnap_moi set name=? where id=?;";
			PreparedStatement pstmt = conn.prepareStatement(moiCompanySql);
			pstmt.setString(1, "/公司="+company.getName());
			pstmt.setLong(2, companyId);
			pstmt.executeUpdate();
			pstmt.close();
			
			//插入company的attribute
			String companyAttrSql = "update m2mnap_moi_attribute set old_value=new_value, new_value=?, last_changed_time=? where moi_id=? and moc_attribute_code=?;";
			pstmt = conn.prepareStatement(companyAttrSql);
			pstmt.setLong(3, companyId);
			pstmt.setTimestamp(2, new Timestamp(new Date().getTime()));
			pstmt.setString(1, company.getName());//公司名称
			pstmt.setString(4, "name");
			pstmt.executeUpdate();
			pstmt.setString(1, company.getCode());//公司编号
			pstmt.setString(4, "Id");
			pstmt.executeUpdate();
			pstmt.setString(1, company.getPer());//联系人
			pstmt.setString(4, "per");
			pstmt.executeUpdate();
			pstmt.setString(1, company.getTel());//联系电话
			pstmt.setString(4, "tel");
			pstmt.executeUpdate();
			pstmt.setString(1, company.getPhone());//座机
			pstmt.setString(4, "phone");
			pstmt.executeUpdate();
			pstmt.setString(1, "硅谷亮城");//地址
			pstmt.setString(4, "addr");
			pstmt.executeUpdate();
			pstmt.setDouble(1, company.getBalance());//预存款
			pstmt.setString(4, "bookSaving");
			pstmt.executeUpdate();
			pstmt.setInt(1, company.getRemindDay());//提醒阈值
			pstmt.setString(4, "remindDay");
			pstmt.executeUpdate();
			pstmt.close();
			//提交
			conn.commit();
			System.out.println("更新记录公司(moi_id, 公司名称, 公司编号, 联系人, 联系电话, 座机, 预存款, 提醒阈值) values ("
					+ company.getId() +", " + company.getName() +", " + company.getId() +", " + company.getPer() +", " + company.getTel() +", "
					+ company.getPhone() +", " + company.getBalance() +", " + company.getRemindDay() + ");");
		} catch (SQLException e) {
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		System.out.println("CompanyManager save() called.");
	}
	
	/**
	 * 用业主公司名称查找company 返回业主公司信息的一个集合，数据库查询时使用通配符进行查询
	 * 
	 * @param String
	 *            name
	 */
	public static ArrayList<Company> findByName(String comname){
		//连接数据库
		ArrayList<Company> ac = new ArrayList<Company>();
		Connection conn = DataBaseManager.getConnection();
		try {
			//禁止自动提交
			conn.setAutoCommit(false);
			//按公司名称查找公司moi_id
			ArrayList<Long> moi = new ArrayList<>();
			String moiFindIdSql = "select id from m2mnap_moi where native_ems_name like ?";
			PreparedStatement pstmt = conn.prepareStatement(moiFindIdSql);
			pstmt.setString(1, "%"+comname+"%");
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				moi.add(rs.getLong(1));
			}
			rs.close();
			pstmt.close();
			
			ac = getCompanies(moi,conn);
			//提交
			conn.commit();
		} catch (SQLException e) {
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return ac;
	}
	
	/**
	 * 用联系人查找company，返回业主信息的一个集合,数据库查询时使用通配符进行查询
	 * 
	 * @param String
	 *           per
	 */
	public static ArrayList<Company> findByPer(String per){
		//连接数据库
		ArrayList<Company> ac = new ArrayList<Company>();
		Connection conn = DataBaseManager.getConnection();
		try {
			//禁止自动提交
			conn.setAutoCommit(false);
			//按联系人查找公司moi_id
			ArrayList<Long> moi = new ArrayList<>();
			String moiFindIdSql = "select moi_id from m2mnap_moi_attribute where new_value like ?";
			PreparedStatement pstmt = conn.prepareStatement(moiFindIdSql);
			pstmt.setString(1, "%"+per+"%");
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				//System.out.println(rs.getLong(1));
				moi.add(rs.getLong(1));
			}
			rs.close();
			pstmt.close();

			ac = getCompanies(moi,conn);
			//提交
			conn.commit();
		} catch (SQLException e) {
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return ac;
	}
	
	//用房间号来查询 业主公司的信息，返回业主信息的一个集合
	public static ArrayList<Company> findByRoom(Long room_moi_id){
		ArrayList<Company> ac = new ArrayList<Company>();
		Connection conn = DataBaseManager.getConnection();
		try {
			//禁止自动提交
			conn.setAutoCommit(false);
			Long company_moi_id = null;
			//按房间moi_id查找公司moi_id
			ArrayList<Long> moi = new ArrayList<>();
			String moiFindIdSql = "select a_moi_id from m2mnap_moi_rs "
					+ "where a_moc_id =? and z_moc_id=? and z_moi_id =?";			
			PreparedStatement pstmt = conn.prepareStatement(moiFindIdSql);
			pstmt.setLong(1, Constants.MOC_COMPANY_ID);
			pstmt.setLong(2, Constants.MOC_ROOM_ID);
			pstmt.setLong(3, room_moi_id);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				company_moi_id = rs.getLong(1);
				System.out.println(company_moi_id);
				moi.add(company_moi_id);
				ac = getCompanies(moi,conn);
			}
			//提交
			conn.commit();
		} catch (SQLException e) {
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return ac;
	}
	

	//给定公司的moi_id,查找公司的全部信息,返回一个公司信息的集合
	public static ArrayList<Company> getCompanies(ArrayList<Long> moi, Connection conn) {
		ArrayList<Company> ac = new ArrayList<Company>();
		try {
			for (Long m : moi) {
				Company comp = new Company();
				String sqlString = "select new_value from m2mnap_moi_attribute where moi_id = ? and moc_attribute_code=?";
				PreparedStatement pstmt = conn.prepareStatement(sqlString);
				pstmt.setLong(1, m);

				pstmt.setString(2, "name");
				ResultSet rs = pstmt.executeQuery();
				if (rs.next()) {
					comp.setName(rs.getString(1));
				}

				pstmt.setString(2, "Id");
				rs = pstmt.executeQuery();
				if (rs.next()) {
					comp.setCode(rs.getString(1));
				}

				pstmt.setString(2, "per");
				rs = pstmt.executeQuery();
				if (rs.next()) {
					comp.setPer(rs.getString(1));
				}

				pstmt.setString(2, "tel");
				rs = pstmt.executeQuery();
				if (rs.next()) {
					comp.setTel(rs.getString(1));
				}

				pstmt.setString(2, "phone");
				rs = pstmt.executeQuery();
				if (rs.next()) {
					comp.setPhone(rs.getString(1));
				}

				pstmt.setString(2, "bookSaving");
				rs = pstmt.executeQuery();
				if (rs.next()) {
					comp.setBalance(Double.parseDouble(rs.getString(1)));
				}

				pstmt.setString(2, "remindDay");
				rs = pstmt.executeQuery();
				if (rs.next()) {
					comp.setRemindDay(Integer.parseInt(rs.getString(1)));
				}

				if(comp.getName()!=null){
					ac.add(comp);
					System.out.println(comp.getName());
				}
				comp.setId(m);
				//System.out.println(comp.getId());
				rs.close();
				pstmt.close();
			}
			
		} catch (SQLException e) {
			try {
				// 回滚
				conn.rollback();
				
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		return ac;
	}
	
	/**
	 * 得到所有company的list
	 * @return
	 */
	public static List<Company> getAllCompanies(){
		List<Company> companies = new ArrayList<Company>();
		//连接数据库
		Connection conn = DataBaseManager.getConnection();
		try {
			String sql = "select * from view_moc_company;";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				Company company = new Company();
				company.setId(rs.getLong("moi_id"));
				company.setName(rs.getString("name"));
				company.setPer(rs.getString("per"));
				company.setTel(rs.getString("tel"));
				companies.add(company);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("CompanyManager save() called.");
		return companies;
	}

	public static void main(String[] args) {
		//CompanyManager.findByName("中国移动");
		CompanyManager.findByRoom(4823L);
	}
}
